package model.dba;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import model.Alumno;
import model.Contacto;
import model.Estado_Evento;
import model.Evento;
import model.PersonalAdministrativo;

public class EventoDAO extends JDBC {
	private static EventoDAO eventoDAO;

	private EventoDAO() {
		super();
	}

	public static EventoDAO getInstance() {
		if (eventoDAO == null) {
			eventoDAO = new EventoDAO();
		}
		return eventoDAO;
	}

	public int add(Evento evento) {

		try {
			String queryString = "INSERT INTO eventos(estado ,fechaCreacion ,fechaEvento ,comentario ,dniPersonalAdmin, idContactoInicio) VALUES(? ,? ,? ,? ,? ,?)";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, evento.getEstado().toString());
			preparedStatement.setTimestamp(2, new Timestamp(evento.getFechaCreacion().getTimeInMillis()));
			preparedStatement.setTimestamp(3, new Timestamp(evento.getFechaEvento().getTimeInMillis()));
			preparedStatement.setString(4, evento.getComentario());
			preparedStatement.setString(5, evento.getPersonalAdministrativo() != null ? evento.getPersonalAdministrativo().getDNI() : null);
			preparedStatement.setLong(6, evento.getContactoInicial().getId());

			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return getLastId();

	}

	public boolean update(Evento evento) {
		boolean state = false;
		try {
			String queryString = "UPDATE eventos SET  estado =?, fechaCreacion =?, fechaEvento =?, comentario =?, dniPersonalAdmin =?, idContactoInicio =?, motivo =?";

			if (evento.getContactoFinalizacion() != null) {
				queryString += ", idContactoFin=? WHERE idEvento=?";
				preparedStatement = connection.prepareStatement(queryString);
				preparedStatement.setLong(8, evento.getContactoFinalizacion().getId());
				preparedStatement.setInt(9, evento.getId());

			} else {
				queryString += " WHERE idEvento=?";
				preparedStatement = connection.prepareStatement(queryString);
				preparedStatement.setInt(8, evento.getId());
			}
			preparedStatement.setString(1, evento.getEstado().toString());
			preparedStatement.setTimestamp(2, new Timestamp(evento.getFechaCreacion().getTimeInMillis()));
			preparedStatement.setTimestamp(3, new Timestamp(evento.getFechaEvento().getTimeInMillis()));
			preparedStatement.setString(4, evento.getComentario());
			preparedStatement.setString(5, evento.getPersonalAdministrativo().getDNI());
			preparedStatement.setLong(6, evento.getContactoInicial().getId());
			preparedStatement.setString(7, evento.getMotivo());

			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return state;
	}

	public boolean delete(Evento evento) {
		boolean state = false;

		try {
			String queryString = "DELETE FROM eventos WHERE idEvento=?";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setInt(1, evento.getId());
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return state;

	}

	public Evento get(Integer id) {

		Evento evento = new Evento();
		try {
			String queryString = "SELECT * FROM eventos where idEvento = '" + id + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				evento = populate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return evento;
	}

	public int getLastId() {
		int ret = 0;
		try {
			String queryString = "SELECT idEvento FROM eventos order by idEvento DESC LIMIT 1  ";// TODO borrre idEvento

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = resultSet.getInt("idEvento");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return ret;

	}

	public int get(PersonalAdministrativo personalAdministrativo, Alumno alumno, String fecha) {
		int ret = 0;

		try {
			String queryString = "SELECT idEvento FROM eventos order idEvento DESC LIMIT 1  ";// TODO borrre idEvento

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = resultSet.getInt("idEvento");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return ret;

	}

	public List<Evento> get(Estado_Evento estado_Evento, PersonalAdministrativo personalAdministrativo) {
		List<Evento> eventoList = new ArrayList<Evento>();

		try {
			String queryString = "SELECT * FROM eventos where estado = '" + estado_Evento.toString() + "' and dniPersonalAdmin= '" + personalAdministrativo.getDNI() + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				eventoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return eventoList;
	}

	public List<Evento> get(Estado_Evento estado_Evento) {
		List<Evento> eventoList = new ArrayList<Evento>();

		try {
			String queryString = "SELECT * FROM eventos where estado = '" + estado_Evento.toString() + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				eventoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return eventoList;
	}

	public List<Evento> get(PersonalAdministrativo personalAdministrativo) {
		List<Evento> eventoList = new ArrayList<Evento>();

		try {
			String queryString = "SELECT * FROM eventos where dniPersonalAdmin= '" + personalAdministrativo.getDNI() + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				eventoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return eventoList;
	}

	public List<Evento> getAll() {
		List<Evento> eventoList = new ArrayList<Evento>();

		try {
			String queryString = "SELECT * FROM eventos  ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				eventoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return eventoList;
	}

	private Evento populate() throws SQLException {
		PersonalAdministrativoDAO personalAdministrativoDAO = PersonalAdministrativoDAO.getInstance();
		ContactoDAO contactoDAO = ContactoDAO.getInstance();

		Evento evento = new Evento();
		evento.setId((int) resultSet.getLong("idEvento"));
		evento.setComentario(resultSet.getString("comentario"));
		evento.setContactoInicial(contactoDAO.get((int) resultSet.getLong("idContactoInicio")));
		Contacto contactoFinalizacion = contactoDAO.get((int) resultSet.getLong("idContactoFin"));
		evento.setContactoFinalizacion(contactoFinalizacion.getId() != null ? contactoFinalizacion : null);
		evento.setEstado(Estado_Evento.valueOf(resultSet.getString("estado") != null ? resultSet.getString("estado").trim() : null));
		GregorianCalendar fechaCreacion = new GregorianCalendar();
		fechaCreacion.setTime(resultSet.getTimestamp("fechaCreacion"));
		evento.setFechaCreacion(fechaCreacion);
		GregorianCalendar fechaEvento = new GregorianCalendar();
		fechaEvento.setTime(resultSet.getTimestamp("fechaEvento"));
		evento.setFechaEvento(fechaEvento);
		evento.setPersonalAdministrativo(personalAdministrativoDAO.get(resultSet.getString("dniPersonalAdmin")));
		evento.setMotivo(resultSet.getString("motivo"));

		return evento;

	}
}